<html>
<!-- =====================================================================

  File:      usp_ProductDetail.htm for Adventure Works Cycles Storefront Sample
  Summary:   Self-documentation for application
  Date:	     June 16, 2003

=====================================================================

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= -->
    <head>
        <title>Adventure Works Cycles Store Documentation</title>
        <link rel="stylesheet" href="style.css">
    </head>
    <body class="NormalIndent">
        <h1>"usp_ProductDetail" Stored Procedure</h1>

        <b>Description:</b>

        <P>This stored procedure accepts a ProductID as an input parameter and returns
        product information to the caller in the form of output parameters. A one line
        recordset is not used in order to gain a performance increase. The data returned by this stored procedures is used
        primarily on the product details screen.</P>

        <b>Definition:</b>

<pre style="background-color:white">
<span style="color: #0000FF"><strong>CREATE</strong></span> Procedure usp_ProductDetail
<span style="color: #4444FF">(</span>
    @ProductID    <span style="color: #2040a0"><strong>int</strong></span><span style="color: #4444FF">,</span>
	@Language	  <span style="color: #2040a0"><strong>nchar</strong></span><span style="color: #4444FF">(</span>2<span style="color: #4444FF">)</span><span style="color: #4444FF">,</span>
	@Culture	  <span style="color: #2040a0"><strong>nvarchar</strong></span> <span style="color: #4444FF">(</span>10<span style="color: #4444FF">)</span><span style="color: #4444FF">,</span>
    @ModelNumber  <span style="color: #2040a0"><strong>nvarchar</strong></span> <span style="color: #4444FF">(</span>25<span style="color: #4444FF">)</span> OUTPUT<span style="color: #4444FF">,</span>
    @ModelName    <span style="color: #2040a0"><strong>nvarchar</strong></span> <span style="color: #4444FF">(</span>50<span style="color: #4444FF">)</span> OUTPUT<span style="color: #4444FF">,</span>
    @ProductImage <span style="color: #2040a0"><strong>nvarchar</strong></span> <span style="color: #4444FF">(</span>50<span style="color: #4444FF">)</span> OUTPUT<span style="color: #4444FF">,</span>
    @UnitCost     <span style="color: #2040a0"><strong>nvarchar</strong></span> <span style="color: #4444FF">(</span>20<span style="color: #4444FF">)</span> OUTPUT<span style="color: #4444FF">,</span>
    @Description  <span style="color: #2040a0"><strong>nvarchar</strong></span> <span style="color: #4444FF">(</span>4000<span style="color: #4444FF">)</span> OUTPUT
<span style="color: #4444FF">)</span>
<span style="color: #0000FF"><strong>AS</strong></span>


<span style="color: #0000FF"><strong>SELECT</strong></span> TOP 1
    @ModelNumber  <span style="color: #4444FF">=</span> P.ProductNumber<span style="color: #4444FF">,</span>
    @ModelName    <span style="color: #4444FF">=</span> P.[<span style="color: #0000FF"><strong>Name</strong></span>]<span style="color: #4444FF">,</span>
    @ProductImage <span style="color: #4444FF">=</span> PP.LargePhotoFileName<span style="color: #4444FF">,</span>
    @UnitCost     <span style="color: #4444FF">=</span> dbo.ConvertCurrency<span style="color: #4444FF">(</span>P.ListPrice<span style="color: #4444FF">,</span> @Culture<span style="color: #4444FF">)</span>.ToString<span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">,</span>
    @Description  <span style="color: #4444FF">=</span> PD.Description

<span style="color: #0000FF"><strong>FROM</strong></span> 
    Production.Product <span style="color: #0000FF"><strong>AS</strong></span> P
 
 LEFT OUTER JOIN Production.ProductProductPhoto <span style="color: #0000FF"><strong>AS</strong></span> PPP <span style="color: #0000FF"><strong>on</strong></span> P.ProductID<span style="color: #4444FF">=</span>PPP.ProductID
 LEFT OUTER JOIN Production.ProductPhoto <span style="color: #0000FF"><strong>AS</strong></span> PP <span style="color: #0000FF"><strong>ON</strong></span> PP.ProductPhotoID<span style="color: #4444FF">=</span>PPP.ProductPhotoID
 LEFT OUTER JOIN Production.ProductModel <span style="color: #0000FF"><strong>AS</strong></span> PM <span style="color: #0000FF"><strong>ON</strong></span> P.ProductModelID<span style="color: #4444FF">=</span>PM.ProductModelID
 LEFT OUTER JOIN Production.ProductModelProductDescriptionCulture <span style="color: #0000FF"><strong>AS</strong></span> PMPDL 
   <span style="color: #0000FF"><strong>ON</strong></span> <span style="color: #4444FF">(</span>PM.ProductModelID<span style="color: #4444FF">=</span>PMPDL.ProductModelID <span style="color: #0000FF"><strong>AND</strong></span> PMPDL.CultureID<span style="color: #4444FF">=</span>@Language<span style="color: #4444FF">)</span>
 LEFT OUTER JOIN Production.ProductDescription <span style="color: #0000FF"><strong>AS</strong></span> PD
   <span style="color: #0000FF"><strong>ON</strong></span> PMPDL.ProductDescriptionID<span style="color: #4444FF">=</span>PD.ProductDescriptionID

<span style="color: #0000FF"><strong>WHERE</strong></span> 
    P.ProductID <span style="color: #4444FF">=</span> @ProductID <span style="color: #0000FF"><strong>AND</strong></span> PPP.[<span style="color: #0000FF"><strong>Primary</strong></span>] <span style="color: #4444FF">=</span> 1

<span style="color: #0000FF"><strong>ORDER BY</strong></span> PP.ModifiedDate ASC;


</pre>

        <b>Database Tables Used: </b>

        <P><i>Product</i>, <i>ProductPhoto</i>, <i>ProductModel</i>, <i>ProductDescription</i>, <i>ProductModelXProductDescriptionXLocale</i>: &nbsp;
        <BR>The Product table contains the core information about all of the items for sale
        on the Adventure Works Cycles web site. Its primary key is the ProductID identity field. 
        <br>The ProductPhoto table contains information about the pictures of the products.
        <br>The ProductModel table contains information about a given type of product (irrespective of such issues as size).
        <br>The ProductDescription table contains textual information about one or more product models.
        <br>The ProductModelXProductDescriptionXLocale table associates particular types of products with information about those kinds of products and the languages for which that information is appropriate.

        <p>

        <img src="1x1.gif" width=25> <IMG src="../docs/images/Product-Photo-Description.gif" width="767" height="681">

    </body>
</html>
